
package store;

import java.sql.Connection; 
import java.sql.DriverManager; 
import java.sql.SQLException; 
import java.sql.ResultSet;
import java.sql.Statement;
import java.sql.PreparedStatement;
import java.sql.Blob;
import java.sql.Types;
import java.util.Vector;
import java.util.Enumeration;
import java.util.Iterator;
import java.util.Hashtable;
import java.io.*;

import index.*;



/**
 * Cette classe permet de creer une BD relationnelle Postgres et de stocker des donnees dans la base
 *
 * Tables creees : Document, Termes, TermesDoc
 */



// Notice, do not import com.mysql.jdbc.* 
// or you will have problems! 

public class BaseWriter { 
     
      static Connection conn;            
     
     /**
     * Constructeur. <br>
     * Effectue une connexion a la base
     * @param ConnectURL String contenant l'URL pour la connection. 
     * @param login Login pour la connection a la base
     * @param pass Mot de passe pour la collection a la base
     */
     public BaseWriter(String ConnectURL, String login, String pass) {
             
         try { 
            

            Class.forName("org.postgresql.Driver").newInstance();
            //Class.forName("com.mysql.jdbc.Driver").newInstance(); 
            System.out.println("Driver charge.");
             } catch (Exception ex) { 
            // handle the error 
         } 
        
        try {
             System.out.println("Tentative de connection..."+ConnectURL+login); 
              conn = DriverManager.getConnection(ConnectURL,login,pass);
             System.out.println("Connection etablie!");  
            
        }
        catch (SQLException ex) {
            // handle any errors 
            System.out.println("SQLException: " + ex.getMessage()); 
            System.out.println("SQLState: " + ex.getSQLState()); 
            System.out.println("VendorError: " + ex.getErrorCode()); 
        }
             
     } // BaseWriter()
     
    
    /**
    * Creee toutes les tables de l'index:
    * 
    * Efface les tables si elles existent deja.
    */
    public static void create() throws SQLException{ 
           
   // assume conn is an already created JDBC connection
   Statement stmt = null; 


    stmt = conn.createStatement(); 
    boolean rs = stmt.execute("drop table if exists Termes, TermesDoc, Documents, Balises, PositionDoc;"); 
    
    rs = stmt.execute("create table Documents (doc_id int primary key, document varchar(300)) ;"); 
	System.out.println("Table Documents creee");
	
	rs = stmt.execute("create table Balises (bal_id int primary key, nom varchar(300), coeff double precision) ;"); 
	System.out.println("Table Balises creee");
	
	rs = stmt.execute("create table Termes (term_id int primary key, term varchar(300) NOT NULL );");
	System.out.println("Table Termes creees");
	
	rs = stmt.execute("create table TermesDoc (term_id int, doc_id int, poids real,PRIMARY KEY(term_id,doc_id));");
    System.out.println("Table TermesDoc creees");
    
    rs = stmt.execute("create table PositionDoc (pos_id int primary key, term_id int, doc_id int, position int,bal_id int);");
    System.out.println("Table PositionDoc creees");
	
    if (stmt != null) { 
        try { 
            stmt.close(); 
        } catch (SQLException sqlEx) { // ignore
        } 

        stmt = null; 
    } 
  
    Vector<Balise> baliseVector = initBalises();
    insertBalises(baliseVector);
   
} //create()



/**
* Insere le contenu d'un vecteur de DocumentAIndexer dans la table Documents.
*@see DocumentAIndexer
*@param myDocumentVector Vecteur d'objets DocumentAIndexer
*/
public static void insertDocument  (Vector myDocumentVector) throws SQLException{
        
   
        // assume conn is an already created JDBC connection
       PreparedStatement stmt=null;
       String query = "insert into Documents (doc_id, document) values (?,?);";
       stmt = conn.prepareStatement(query);
       
       // pour chaque DocumentAIndexer du vecteur
       for (Enumeration e=myDocumentVector.elements(); e.hasMoreElements(); ) {
    	   
            DocumentAIndexer tempDocument=new DocumentAIndexer();
            tempDocument= (DocumentAIndexer) e.nextElement(); 
          
            stmt.setInt(1,tempDocument.id);
            stmt.setString(2, tempDocument.name);
           
            try {
            			stmt.addBatch();
            }
            catch (SQLException sqlEx) { System.out.println("Erreur dans l'insertion dans Documents : "+ sqlEx.getMessage());
            } 
              
           }
       
        if (stmt != null) { 	
        try { 
        	stmt.executeBatch();
            stmt.close(); 
        } catch (SQLException sqlEx) { System.out.println("Erreur dans l'insertion dans Documents : "+ sqlEx.getMessage());
        } 

    } 
      
   
} // insertDocument()



/**
 * Permet d'initialiser dans la base toutes les balises utiles et les coefficients de scoring associés 
 */
 public static Vector<Balise> initBalises() {
	 Vector<Balise> baliseVector = new Vector<Balise>();
	 baliseVector.add(new Balise(0, "body", 0.5));
	 baliseVector.add(new Balise(1, "title", 10));
	 baliseVector.add(new Balise(2, "h1", 5));
	 baliseVector.add(new Balise(3, "h2", 4));
	 baliseVector.add(new Balise(4, "h3", 3));
	 baliseVector.add(new Balise(5, "meta keywords", 8));
	 baliseVector.add(new Balise(6, "meta description", 7));
	 baliseVector.add(new Balise(7, "meta classification", 9));
	 baliseVector.add(new Balise(8, "img alt", 2));
	 baliseVector.add(new Balise(9, "img title", 1.5));
	 baliseVector.add(new Balise(10, "caption", 1.5));
	 baliseVector.add(new Balise(11, "table summary", 2));
	 baliseVector.add(new Balise(12, "Strong", 1));
	 baliseVector.add(new Balise(13, "a title", 0.75));	
	 
	 return baliseVector;
 }

/**
* Insere le contenu d'un vecteur de Balise dans la table Balise.
*@see Balise
*@param myBaliseVector Vecteur d'objets Balise
*/
public static void insertBalises  (Vector myBaliseVector) throws SQLException{
		
		
        // assume conn is an already created JDBC connection
       PreparedStatement stmt=null;
       String query = "insert into Balises (bal_id, nom, coeff) values (?,?,?);";
       stmt = conn.prepareStatement(query);
       
       // pour chaque Balise du vecteur
       for (Enumeration e=myBaliseVector.elements(); e.hasMoreElements(); ) {
    	    
            Balise tempBalise= (Balise) e.nextElement(); 
            
            stmt.setInt(1,tempBalise.bal_id);
            stmt.setString(2, tempBalise.nom);
            stmt.setDouble(3, tempBalise.coeff);
            
            try {
            	stmt.addBatch();
            	}
            catch (SQLException sqlEx) { System.out.println("Erreur dans l'insertion dans Balises : "+ sqlEx.getMessage());} 
           }
        if (stmt != null) { 
        try { 
        	stmt.executeBatch();
            stmt.close();
            System.out.println("Table Balises Mise à jour");
        } catch (SQLException sqlEx) { System.out.println("Erreur dans l'insertion dans Balises : "+ sqlEx.getMessage());
        } 

    } 
      
   
} // insertDocument()






/**
*Insere le contenu d'une hashtable de termes (objets Term) dans la table Termes et dans la table TermesDoc
* 
*@see Term
*@param myPostingTable Hashtable d'objets Term
*/
public static void insertPosting(Hashtable myPostingTable) throws SQLException{
        
		//pour les positions
		int countPositionDoc = 0;
		
		//Inialisation des preparestatement
        // assume conn is an already created JDBC connection
       //Statement stmt = null; 
	   PreparedStatement stmt_term=null;
	   String query = "insert into Termes (term_id,term) values (?,?)";
	   stmt_term = conn.prepareStatement(query);
	  
	   PreparedStatement stmt_termDoc=null;
	   String query2 = "insert into TermesDoc (term_id,doc_id,poids) values (?,?,?)";
	   stmt_termDoc = conn.prepareStatement(query2);
	   
	   PreparedStatement stmt_position=null;
	   String query3 = "insert into PositionDoc (pos_id, term_id, doc_id, position,bal_id) values (?,?,?,?,?)";
	   stmt_position = conn.prepareStatement(query3);
	   
       // for each Term in the hashtable 
       for (Enumeration e=myPostingTable.elements(); e.hasMoreElements(); ) {
               
                Term tempTerm=new Term();
                tempTerm = (Term) e.nextElement();
                
                stmt_term.setInt(1,tempTerm.term_id);
                stmt_term.setString(2, tempTerm.text);
                
                try {stmt_term.addBatch();}
                catch (SQLException sqlEx) {afficherException(sqlEx);}         
               
                //insertion dans la table TermesDoc
               for (Iterator it = tempTerm.frequency.keySet().iterator(); it.hasNext(); ){
                                TermFrequency tempTermFrequency=new TermFrequency();
                                tempTermFrequency = (TermFrequency) tempTerm.frequency.get(it.next()); 
                                
                                stmt_termDoc.setInt(1,tempTerm.term_id);
                                stmt_termDoc.setInt(2, tempTermFrequency.doc_id);
                                stmt_termDoc.setShort(3, tempTermFrequency.frequency);
                                
                                try {stmt_termDoc.addBatch();}
                                catch (SQLException sqlEx){afficherException(sqlEx);}
                        }  // for it
               
               //insertion dans la table Position Document
               for (Iterator ite = tempTerm.positionDoc.iterator(); ite.hasNext(); ){
                   
                   PositionDoc tempPositionDoc = (PositionDoc) ite.next(); 
                   
                   stmt_position.setInt(1,countPositionDoc);
                   stmt_position.setInt(2, tempTerm.term_id);
                   stmt_position.setInt(3, tempPositionDoc.doc_id);
                   stmt_position.setInt(4,tempPositionDoc.position);
                   stmt_position.setInt(5, tempPositionDoc.bal_id);
                   
                   try {stmt_position.addBatch();} 
                   catch (SQLException e2) {afficherException(e2);}
                   
                   countPositionDoc++;
                   }  
             } //enumeration
       
   
    //On effectue les insertion dans les tables en un seul coup!!!   
    if ((stmt_term != null)&&(stmt_position != null)&&(stmt_termDoc != null)) { 
        try {
        	stmt_term.executeBatch();
        	System.out.println("Insertion dans la table Termes : ok");
        	stmt_termDoc.executeBatch();
        	System.out.println("Insertion dans la table TermesDoc : ok");
        	stmt_position.executeBatch();
        	System.out.println("Insertion dans la table PositionDoc : ok");
        	
        	stmt_term.close();
        	stmt_termDoc.close();
        	stmt_position.close();
        } catch (SQLException sqlEx) { 
        	afficherException(sqlEx);
        }
        stmt_term = null;
        stmt_termDoc = null;
    	stmt_position = null;       
    } //stmt
    //System.out.println("Insertion dans la table Termes : ok");
 
        
} // insertPosting()


/**
* Ferme la connection au serveur mySQL
*/
public static void close() {
     try {
          conn.close();     
           
        } catch (SQLException ex) {
            // handle any errors 
            System.out.println("SQLException: " + ex.getMessage()); 
            System.out.println("SQLState: " + ex.getSQLState()); 
            System.out.println("VendorError: " + ex.getErrorCode()); 
        }   
} //close()

/**
 * Affiche exception sql
 */
public static void afficherException (SQLException sqlEx) {
	System.out.println("Exception SQL : ");
	   while (sqlEx != null) {
	      String message = sqlEx.getMessage();
	      String sqlState = sqlEx.getSQLState();
	      int errorCode = sqlEx.getErrorCode();
	      System.out.println("Message = "+message);
	      System.out.println("SQLState = "+sqlState);
	      System.out.println("ErrorCode = "+errorCode);
	      sqlEx.printStackTrace();
	      sqlEx = sqlEx.getNextException();
	   }
}


 
 
} // BaseWriter.java
